﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

using DM_Article.Model;

namespace DM_Article.Bll
{
    public class articlebll
    {
        public static int add(OleDbConnection conn, article model)
        {
            OleDbCommand odc = new OleDbCommand();
            odc.CommandText = "insert into article(title,keyword,content,bigclassid,smallclassid,origin,author,addtime,hits)values('"
                                + model.title + "','" + model.keyword + "','" + model.content + "'," + model.bigclassid + "," + model.smallclassid + ",'" + model.origin + "','" + model.author + "','" + model.addtime + "',0)";
            odc.Connection = conn;
            return odc.ExecuteNonQuery();
        }

        public static int update(OleDbConnection conn, article model)
        {
            OleDbCommand odc = new OleDbCommand();
            odc.CommandText = "update article set title='" + model.title + "',keyword ='" + model.keyword + "',content='" + model.content + "',bigclassid=" + model.bigclassid + ",smallclassid=" + model.smallclassid + ",origin='" + model.origin + "',author='" + model.author + "',addtime='" + model.addtime + "' where id=" + model.id;
            odc.Connection = conn;
            return odc.ExecuteNonQuery();
        }

        public static int delete(OleDbConnection conn, string id)
        {
            OleDbCommand odc = new OleDbCommand();
            odc.CommandText = "delete from article where id=" + id;
            odc.Connection = conn;
            return odc.ExecuteNonQuery();
        }

        public static int deleteBySmallclass(OleDbConnection conn, string smallclassid)
        {
            OleDbCommand odc = new OleDbCommand();
            odc.CommandText = "delete from article where smallclassid=" + smallclassid;
            odc.Connection = conn;
            return odc.ExecuteNonQuery();
        }

        public static int deleteByBigclass(OleDbConnection conn, string bigclassid)
        {
            OleDbCommand odc = new OleDbCommand();
            odc.CommandText = "delete from article where bigclassid=" + bigclassid;
            odc.Connection = conn;
            return odc.ExecuteNonQuery();
        }

        public static article getArticleDetail(OleDbConnection conn, string id)
        {
            OleDbDataAdapter odda = new OleDbDataAdapter();
            odda.SelectCommand = new OleDbCommand();
            odda.SelectCommand.CommandText = "select [title],[keyword],[content],[bigclassid],[smallclassid],[origin],[author],[addtime],[hits]  from article where id=" + id;
            odda.SelectCommand.Connection = conn;
            DataTable dt = new DataTable();
            odda.Fill(dt);

            article model = new article();
            model.id = Convert.ToInt32(id);
            if (dt != null && dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["title"].ToString() != "")
                {
                    model.title = dt.Rows[0]["title"].ToString();
                }
                if (dt.Rows[0]["keyword"].ToString() != "")
                {
                    model.keyword = dt.Rows[0]["keyword"].ToString();
                }
                if (dt.Rows[0]["content"].ToString() != "")
                {
                    model.content = dt.Rows[0]["content"].ToString();
                }
                if (dt.Rows[0]["bigclassid"].ToString() != "")
                {
                    model.bigclassid = Convert.ToInt32(dt.Rows[0]["bigclassid"].ToString());
                }
                if (dt.Rows[0]["smallclassid"].ToString() != "")
                {
                    model.smallclassid = Convert.ToInt32(dt.Rows[0]["smallclassid"].ToString());
                }
                if (dt.Rows[0]["origin"].ToString() != "")
                {
                    model.origin = dt.Rows[0]["origin"].ToString();
                }
                if (dt.Rows[0]["author"].ToString() != "")
                {
                    model.author = dt.Rows[0]["author"].ToString();
                }
                if (dt.Rows[0]["addtime"].ToString() != "")
                {
                    model.addtime = dt.Rows[0]["addtime"].ToString();
                }
                if (dt.Rows[0]["hits"].ToString() != "")
                {
                    model.hits = Convert.ToInt32(dt.Rows[0]["hits"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }

        }

        public static DataTable getArticleTable(OleDbConnection conn)
        {
            OleDbDataAdapter odda = new OleDbDataAdapter();
            odda.SelectCommand = new OleDbCommand();
            odda.SelectCommand.CommandText = "select [id],[title],[content],[bigclassid],[smallclassid],[origin],[author],[addtime],[hits] from article order by addtime DESC";
            odda.SelectCommand.Connection = conn;
            DataTable dt = new DataTable();
            odda.Fill(dt);
            return dt;
        }

        public static DataTable getArticleByHitsTable(OleDbConnection conn, int top)
        {
            OleDbDataAdapter odda = new OleDbDataAdapter();
            odda.SelectCommand = new OleDbCommand();
            odda.SelectCommand.CommandText = "select top " + top + " [hits],[id],[title],[bigclassid],[smallclassid],[origin],[author],[addtime] from article order by hits DESC";
            odda.SelectCommand.Connection = conn;
            DataTable dt = new DataTable();
            odda.Fill(dt);
            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn">database connectting string</param>
        /// <param name="smallclassid">small class id</param>
        /// <param name="top">get top data item from database. if [top] is minus, return all data records.</param>
        /// <returns>return type is 'DataSet'</returns>
        public static DataSet getArticleBySmallclassidDatasetTop(OleDbConnection conn, int smallclassid, int top)
        {
            OleDbDataAdapter odda = new OleDbDataAdapter();
            odda.SelectCommand = new OleDbCommand();
            if (top < 0)
            {
                odda.SelectCommand.CommandText = "select [id],[title],[addtime] from article where smallclassid=" + smallclassid + " order by addtime DESC";
            }
            else
            {
                odda.SelectCommand.CommandText = "select TOP " + top + " [id],[title],[addtime] from article where smallclassid=" + smallclassid + " order by addtime DESC";
            }
            odda.SelectCommand.Connection = conn;
            DataSet ds = new DataSet();
            odda.Fill(ds);
            return ds;
        }

        public static DataSet getArticleByBigclassidDatasetTop(OleDbConnection conn, int bigclassid, int top)
        {
            OleDbDataAdapter odda = new OleDbDataAdapter();
            odda.SelectCommand = new OleDbCommand();
            if (top < 0)
            {
                odda.SelectCommand.CommandText = "select [id],[title],[addtime] from article where bigclassid=" + bigclassid + " order by addtime DESC";
            }
            else
            {
                odda.SelectCommand.CommandText = "select TOP " + top + " [id],[title],[addtime] from article where bigclassid=" + bigclassid + " order by addtime DESC";
            }
            odda.SelectCommand.Connection = conn;
            DataSet ds = new DataSet();
            odda.Fill(ds);
            return ds;
        }

        public static DataSet getArticleSmallclassNameJDXW(OleDbConnection conn, int top)
        {
            string name = "焦点新闻";
            OleDbDataAdapter odda = new OleDbDataAdapter();
            odda.SelectCommand = new OleDbCommand();
            if (top < 0)
            {
                odda.SelectCommand.CommandText = "SELECT id, title, addtime FROM article "
                                                    + " INNER JOIN smallclass ON article.smallclassid = smallclass.smallclassid "
                                                    + " where smallclass.smallclassname = '" + name + "' "
                                                    + " order by article.addtime DESC";

            }
            else
            {
                odda.SelectCommand.CommandText = "SELECT TOP " + top
                                                    + " id, title, addtime FROM article "
                                                    + " INNER JOIN smallclass ON article.smallclassid = smallclass.smallclassid "
                                                    + " where smallclass.smallclassname = '" + name + "' "
                                                    + " order by article.addtime DESC";
            }
            odda.SelectCommand.Connection = conn;
            DataSet ds = new DataSet();
            odda.Fill(ds);
            return ds;
        }

        public static int increaseHits(OleDbConnection conn, int articleId)
        {
            OleDbDataAdapter odda = new OleDbDataAdapter();
            odda.SelectCommand = new OleDbCommand();
            odda.SelectCommand.CommandText = "select [hits]  from article where id=" + articleId;
            odda.SelectCommand.Connection = conn;
            DataTable dt = new DataTable();
            odda.Fill(dt);

            int hits = Convert.ToInt32(dt.Rows[0].ItemArray[0].ToString());
            hits++;

            OleDbCommand odc = new OleDbCommand();
            odc.CommandText = "update article set hits=" + hits + " where id=" + articleId;
            odc.Connection = conn;
            //odc.Connection.Open();
            return odc.ExecuteNonQuery();

        }
    }
}
